IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_DeleteMasterDepartment')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_DeleteMasterDepartment
GO

/****** Object:  StoredProcedure [dbo].[USP_DeleteMasterDepartment]    Script Date: 10/21/2010 21:22:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		AKE
-- Create date: 11:49 13/9/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_DeleteMasterDepartment]
	-- Add the parameters for the stored procedure here
	@tbl nvarchar(max), -- Table's name to delete
	@pid nvarchar(max), -- Primary key's table to delete
	@cid nvarchar(max)  -- Series of identities to delete
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- ==========================================
	-- Validate data before deleting
	-- ==========================================
	declare @tbl_ids table (position int,value int)
	insert	into @tbl_ids (position,value)
	select	position,value from dbo.UFN_Split(@cid,',') as t
	
	
	declare @loop_id int
	declare @no_delete int
	select	@loop_id = count(*), @no_delete = count(*)
	from	@tbl_ids
	
	while (@loop_id > 0) begin
	
		declare @loop_tid int
		select	@loop_tid = value
		from	@tbl_ids
		where	position = @loop_id
	
		-- ========================================
		-- This section for calling validate SP
		-- ========================================
		declare @master_department_level as int
		select @master_department_level = master_department_level
		from master_department where master_department_code = @loop_tid
		
		update master_department
		set master_department_level = master_department_level - 1
		where master_department_level  >= @master_department_level
		
		delete from master_department where master_department_code = @loop_tid

		set @loop_id = @loop_id - 1;
	end

END

GO
